package Server;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DBUtil.CNN;
import WindowsModule.UserInfoTakeing;
import WindowsModule.Ziyuanpinglunxinxifengzhuang;
import WindowsModule.ZiyuanxinxiFenzhuang;

public class UserDBHandle {
	private Connection con;
	private PreparedStatement pst;
	private ResultSet rs;
	private int count;

	public UserDBHandle() {
		this.con = CNN.getConn();
	}

	// 关闭资源的方法
	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (pst != null) {
				pst.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 通过编号获得省名
	 * 
	 * @param id
	 * @return
	 */
	public String getProvince(int id) {
		con = CNN.getConn();
		String sql = "select * from province where id=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				return rs.getString("province");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return "";
	}

	// 获得下拉框中的省
	public List<String> getProvinces() {
		List<String> list = new ArrayList<String>();
		this.con = CNN.getConn();
		try {
			String sql = "select * from dbo.province";
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				String shen = rs.getString("province");
				list.add(shen);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return list;
	}

	// 注册
	public boolean isInsert(String id, String password, String name, String sex, int year, int month, int day, String xingzuo, String aihao, String Email, String Tiwen, String daan, String blood, int province, String city, String myself, String introId) {
		con = CNN.getConn();
		boolean b = false;
		try {
			String sql = " insert into users(id,pwd,name,sex,Birthday,constellation,interest,Email,Tiwen,daan,blood,province,city,explain,introId)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pst = con.prepareStatement(sql);
			pst.setString(1, id);
			pst.setString(2, password);
			pst.setString(3, name);
			pst.setString(4, sex);
			pst.setDate(5, new java.sql.Date(year - 1900, month - 1, day));
			pst.setString(6, xingzuo);
			pst.setString(7, aihao);
			pst.setString(8, Email);
			pst.setString(9, Tiwen);
			pst.setString(10, daan);
			pst.setString(11, blood);
			pst.setInt(12, province);
			pst.setString(13, city);
			pst.setString(14, myself);
			pst.setString(15, introId.equals("") ? null : introId);
			// System.out.println(id + password + name + sex + + myear + month
			// day xingzuo + aihao + Email + Tiwen + daan + blood + province
			// + city + myself + introId);
			count = pst.executeUpdate();
			if (count != 0) {
				b = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return b;
	}

	// 判断用户登录时的信息是否正确
	public boolean isSelect(String id) {
		con = CNN.getConn();
		boolean b = false;
		try {
			String sql = "select id from users where id=?";
			pst = con.prepareStatement(sql);
			pst.setString(1, id);
			rs = pst.executeQuery();
			b = rs.next();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return b;
	}

	public boolean isSelect(String id, String passwrod) {
		boolean b = false;
		con = CNN.getConn();
		try {
			String sql = "select count(*) from users where id=? and pwd=?";
			pst = con.prepareStatement(sql);
			pst.setString(1, id);
			pst.setString(2, passwrod);
			rs = pst.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
			if (count != 0) {
				b = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return b;
	}

	// 修改安全设置
	public boolean isUpdate(String pwdnew, String Tiwen, String daan, String id, String pwdold) {
		boolean b = true;
		con = CNN.getConn();
		try {
			String sql = "update users set pwd=?,Tiwen=?,daan=? where id=? and pwd=?";
			pst = con.prepareStatement(sql);
			pst.setString(1, pwdnew);
			pst.setString(2, Tiwen);
			pst.setString(3, daan);
			pst.setString(4, id);
			pst.setString(5, pwdold);
			count = pst.executeUpdate();
			if (count != 0) {
				b = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return b;
	}

	// 修改个人资料
	public boolean isUpdateInfo(String name, String sex, long birthday, String aihao, String blood, String xingzuo, int province, String city, String Email, String explain, String id) {
		boolean b = false;
		con = CNN.getConn();
		try {
			String sql = "update users set name=?,sex=?,Birthday=?,interest=?,blood=?,constellation=?,province=?,city=?,Email=?,explain=? where id=?";
			pst = con.prepareStatement(sql);
			pst.setString(1, name);
			pst.setString(2, sex);
			pst.setDate(3, new java.sql.Date(birthday));
			pst.setString(4, aihao);
			pst.setString(5, blood);
			pst.setString(6, xingzuo);
			pst.setInt(7, province);
			pst.setString(8, city);
			pst.setString(9, Email);
			pst.setString(10, explain);
			pst.setString(11, id);
			count = pst.executeUpdate();
			if (count != 0) {
				b = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return b;
	}

	//获得评论数量
	public Integer pinglunshuliang(String resMd5) {
		con = CNN.getConn();
		String sql = "select count(*) from resComment where resMd5=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, resMd5);
			rs = pst.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return count;

	}

public Ziyuanpinglunxinxifengzhuang selectpinglun(String userId) {
		con = CNN.getConn();
		Ziyuanpinglunxinxifengzhuang z = null;
		String sql = "select id,[content],Time,Face from resComment inner join users on resComment.userId=users.id where=userId";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, userId);
			rs = pst.executeQuery();
			while (rs.next()) {
				String id = rs.getString(1);
				String content = rs.getString(2);
				String time = rs.getString(3);
				String face = rs.getString(4);
				z = new Ziyuanpinglunxinxifengzhuang(id, content, time, face);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return z;
	}

	public boolean selectUser(String id, StringBuffer oldpassword, StringBuffer tiwen, StringBuffer daan, StringBuffer name, StringBuffer sex, Date Birthday, StringBuffer aihao, StringBuffer blood, StringBuffer xizuo, StringBuffer province, StringBuffer city, StringBuffer Email, StringBuffer explain) {
		boolean b = false;
		con = CNN.getConn();
		try {
			String sql = "select * from users where id=?";
			pst = con.prepareStatement(sql);
			pst.setString(1, id);
			rs = pst.executeQuery();
			if (b = rs.next()) {
				// 安全设置
//				oldpassword.append(rs.getString("pwd"));// 密码
				tiwen.append(rs.getString("Tiwen"));
				daan.append(rs.getString("daan"));
				// 个人资料
				name.append(rs.getString("name"));
				sex.append(rs.getString("sex"));
				Date t = rs.getDate("Birthday");
				Birthday.setYear(t.getYear());
				Birthday.setMonth(t.getMonth());
				Birthday.setDate(t.getDate());
				aihao.append(rs.getString("interest"));
				blood.append(rs.getString("blood"));
				xizuo.append(rs.getString("constellation"));
				province.append(String.valueOf(rs.getInt("province")));
				city.append(rs.getString("city"));
				Email.append(rs.getString("Email"));
				explain.append(rs.getString("explain"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return b;
	}

// 查询个人聊天室的用户信息
	public UserInfoTakeing selectUserInfo(String id) {
		con = CNN.getConn();
		UserInfoTakeing uit = null;
		String sql = "select name,sex,province,city,Email from dbo.users where id=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, id);
			rs = pst.executeQuery();
			while (rs.next()) {
				String name = rs.getString(1);
				String sex = rs.getString(2);
				String address = rs.getString(3) + "/" + rs.getString(4);
				String Email = rs.getString(5);

				uit = new UserInfoTakeing(name, sex, address, Email);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return uit;
	}

	// 找回密码的方法
	public boolean zhaohuimima(String id, String Tiwen, String daan, String password) {
		boolean b = false;
		con = CNN.getConn();
		String sql = "update users set pwd=? where id=? and Tiwen=? and  daan=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, password);
			pst.setString(2, id);
			pst.setString(3, Tiwen);
			pst.setString(4, daan);
			count = pst.executeUpdate();
			if (count != 0) {
				b = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return b;
	}

	//获得资源价值的方法
	public Float ziyuanjiazi(String MD5) {
		float co = 0;
		con = CNN.getConn();
		String sql = "select Pvalue,Ddegree,record from  resInfo where Md5=?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, MD5);
			rs = pst.executeQuery();
			if (rs.next()) {
				float Pvalue = rs.getFloat(1);// 人气值
				float Ddegree = rs.getFloat(2);// 下载次数
				float record = rs.getFloat(3);// 举报次数
				co = (float) (((1 - record / Pvalue) - (1 - Ddegree) / Pvalue) * 0.5);
				float a = 1 - record / Pvalue;
				float b = (float) ((1 - Ddegree) / Pvalue * 0.5);
//				System.out.println(a+"+++++++++++++"+b);
//				System.out.println(Pvalue+"   "+Ddegree+"   "+record);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return co;

	}

	// TODO Auto-generated method stub

	// 查询资源信息窗口中的资源信息
	public ZiyuanxinxiFenzhuang Ziyuanxinxi(String MD5) {
		ZiyuanxinxiFenzhuang z = null;
		con = DBUtil.CNN.getConn();
		try {
			String sql = "SELECT * FROM resInfo WHERE md5=?";
			pst = con.prepareStatement(sql);
			pst.setString(1, MD5);
			rs = pst.executeQuery();
			while (rs != null && rs.next()) {
				String md5 = rs.getString(1);
				String Type = rs.getString(2);
				String Time = rs.getDate(3) + "";
				String Title = rs.getString(4);
				int TypeID = rs.getInt(5);
				String explain = rs.getString(6);
				String flag = rs.getString(7);
				String Length = rs.getString(8);
				float Pipsum = rs.getInt(9);
				float ziyuanshuliang = pinglunshuliang(md5);// 获得评论数量
				float ziyuanjiazi = ziyuanjiazi(md5);

				z = new ZiyuanxinxiFenzhuang(md5, Type, Time, Title, TypeID, explain, flag, Length, Pipsum, ziyuanshuliang, ziyuanjiazi);

			}
		} catch (Exception e) {

		} finally {
			close();
		}
		return z;
	}

	public boolean isUpdateGradeOnlineTime(String userid, int grade, long onlineTime) {
		boolean b = false;
		con = CNN.getConn();
		try {
			String sql = "update users set gradeID=?, online_time=? where id=?";
			pst = con.prepareStatement(sql);
			pst.setInt(1, grade);
			pst.setLong(2, onlineTime);
			pst.setString(3, userid);
			count = pst.executeUpdate();
			if (count != 0) {
				b = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return b;
	}

}
